﻿CREATE procedure [dbo].[spReportSystemVariance]
	@yearPosition int,
	@date datetime
AS

DECLARE @year int
SET @year = YEAR(GETDATE()) + @yearPosition
set ansi_nulls OFF

DECLARE @lastDatePrevious date, @currentDate varchar(10), @previousDate varchar(10), @yearText varchar(4), @weekDay int
DECLARE @query nvarchar(max)

SET @weekDay = DATEPART(weekday, @date)
SET @lastDatePrevious = DATEADD(DAY, CASE @weekDay WHEN 2 THEN -3 ELSE -1 END, @date)

SET @currentDate = CONVERT(varchar(10), @date, 103)
SET @previousDate = CONVERT(varchar(10), @lastDatePrevious, 103)
SET @yearText = CONVERT(varchar(4), @year)

DECLARE @MondayDate date
SELECT @MondayDate = DATEADD(DAY, -@weekDay + 2, CONVERT(date, @date))

DECLARE @value1 VARCHAR(1), @value2 VARCHAR(1)
SET @value1 = CONVERT(varchar(1), @weekDay - 2)
--SET @value2 = CONVERT(varchar(1), @weekDay - CASE @weekDay WHEN 2 THEN 2 ELSE 1 END)
SET @value2 = CONVERT(varchar(1), @weekDay - 1)

SET @query = 'SELECT DISTINCT ' + @yearText +
' as PeriodYear
, ProgramId as [Program Id]
, ProgramName as [Program Name]
, ProgramManager as [Program Manager]
, ProjectId as [Project Id]
, ProjectName as [Project Name]
, AccountLevel1 as [Account Level 1]
, BowFlag as [Bow Flag]
, SystemGOCCode as [System GOC]
, SystemGOC.ManagedGeography as [System Geography Code]
, CONVERT(DECIMAL(38,3), ISNULL(Value' + @value1 + ', 0)) as [' + @previousDate + ']
, 0 as [PreviousVariance]
, CONVERT(DECIMAL(38,3), ISNULL(Value' + @value2 + ', 0)) as [' + @currentDate + ']
, CONVERT(DECIMAL(38,3), ISNULL(Value' + @value2 + ', 0) - ISNULL(Value' + @value1 + ', 0)) as Variance
FROM BookOfWorkSystemVariance variance
INNER JOIN GOC as SystemGOC on SystemGOC.GocAttributes = SystemGocCode AND SystemGOC.ManagedGeography = ''BR''
Where PeriodYear = ' + @yearText + '
	AND variance.ReferenceDate = ''' + CONVERT(varchar(100), @MondayDate) + '''
	AND Value' + @value1 + ' IS NOT NULL AND Value' + @value2 + ' IS NOT NULL'

EXEC (@query)